package deweb.cimaplanning.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.commons.lang.StringUtils;

import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

import deweb.cimaplanning.misc;
import deweb.cimaplanning.dto.POCosts;
import deweb.cimaplanning.dto.POCostsByElements;
import deweb.cimaplanning.dto.PORecurrentCost;

public class POCostsDao extends DBDAO {
	/** Datasource name */
	private static final String dsName = misc.DS_NAME;//$NON-NLS-1$
	
	/**SQL Select all cost elements*/
	protected static final String SQL_SELECT_ALL_ELEMENTS = "SELECT c.purchase_order_version_id, pov.code, pov.code_number,  " 
													+ "		ct.code corn_code, ct.description corn_description, ct.corn_origin_id,  "
													+ "		pov.biweek_period_version_id, p.month_period_version_id,  "
													+ "		SUM(c.amount) amount, pov.capacity "
													+ "	FROM costs_po c "
													+ "		INNER JOIN costs_elements ce "
													+ "			ON c.cost_element_id = ce.cost_element_id "
													+ "		INNER JOIN vw_v_purchases_orders_versions pov "
													+ "			ON c.purchase_order_version_id = pov.purchase_order_version_id "
													+ "		INNER JOIN corns_types ct "
													+ "			ON pov.corn_type_id = ct.corn_type_id "
													+ "		INNER JOIN vw_periods_versions p "
													+ "			ON pov.biweek_period_version_id = p.biweek_period_version_id "
													+ "			AND pov.version_id = p.version_id "
													+ "		LEFT OUTER JOIN transports t "
													+ "			ON pov.transport_id = t.transport_id ";
	
	/**SQL Select all cost elements group by*/
	protected static final String SQL_SELECT_ALL_ELEMENTS_GROUP_BY =  "	GROUP BY c.purchase_order_version_id, pov.code, pov.code_number, pov.capacity,  "
																	+ "		pov.is_transit, ct.code, pov.biweek_period_version_id, p.month_period_version_id,  "
																	+ "		pov.transit_date,  "
																	+ "		ct.description, ct.corn_origin_id, t.priority ";
	
	/**SQL Select all cost elements order by*/
	protected static final String SQL_SELECT_ALL_ELEMENTS_ORDER_BY =  "	ORDER BY pov.biweek_period_version_id, p.month_period_version_id,  "
																	+ "		pov.is_transit DESC, pov.transit_date, t.priority DESC,  "
																	+ "		pov.code, pov.code_number ";
	
	protected static final String SQL_GET_POID_CEID = "SELECT purchase_order_version_id, SUM(amount) amount "
														+ "	FROM costs_po cpo, costs_elements ce "
														+ "	WHERE cpo.cost_element_id = ce.cost_element_id "
														+ "		AND purchase_order_version_id " + PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT
														+ "		AND ce.short_name " + PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT
														+ "	GROUP BY purchase_order_version_id ";
	
	protected static final String SQL_GET_ALL_POID_CEID = "SELECT pov.purchase_order_version_id, cpo.cost_element_id, ce.short_name, SUM(amount) amount "
															+ "	FROM costs_po cpo, costs_elements ce, vw_v_purchases_orders_versions pov "
															+ "	WHERE cpo.cost_element_id = ce.cost_element_id "
															+ "		AND cpo.purchase_order_version_id = pov.purchase_order_version_id "
															+ "		AND pov.version_id " + PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT
															+ "	GROUP BY pov.purchase_order_version_id, cpo.cost_element_id, ce.short_name ";

	protected static final String SQL_GET_ALL_POID_CEID_RECURRENT = "SELECT purchase_order_version_id, pv.plant_version_id, pv.plant_id, pv.month_period_version_id,  "
																			+ "		cpo.cost_element_id, ce.short_name, SUM(cost) amount  "
																			+ "	FROM costs_po_in_plants cpo, costs_elements ce, vw_v_plants_versions pv "
																			+ "	WHERE cpo.cost_element_id = ce.cost_element_id "
																			+ "		AND cpo.plant_version_id = pv.plant_version_id "
																			+ "		AND pv.version_id " + PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT
																			+ "		AND cpo.cost_origin " + PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT
																			+ "	GROUP BY purchase_order_version_id, pv.plant_version_id, pv.plant_id, pv.month_period_version_id, "
																			+ "		cpo.cost_element_id, ce.short_name ";

	protected static final String SQL_GET_ALL_POID_CEID_WAREHOUSE = "SELECT purchase_order_version_id, pv.plant_version_id, pv.plant_id, pv.month_period_version_id,  "
																			+ "		SUM(cost) amount  "
																			+ "	FROM costs_po_in_plants cpo, costs_elements ce, vw_v_plants_versions pv "
																			+ "	WHERE cpo.cost_element_id = ce.cost_element_id "
																			+ "		AND cpo.plant_version_id = pv.plant_version_id "
																			+ "		AND pv.version_id " + PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT
																			+ "		AND ce.short_name IN ('" + misc.WAREHOUSE_IN + "', '" + misc.WAREHOUSE_OUT + "', '" + misc.WAREHOUSE_MONTH + "') "
																			+ "	GROUP BY purchase_order_version_id, pv.plant_version_id, pv.plant_id, pv.month_period_version_id ";


	public POCostsDao() {
	}
	
	/**
	 * Get all the cost elements
	 * @return
	 * @throws Exception
	 */
	public ArrayList getArray(String filters) throws Exception {
		StringBuffer sql = null;
		POCosts dto = null;
		ArrayList array	= null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try{
			array = new ArrayList(100);
			
			//la versionId viene integrada en el filters
			
			sql = new StringBuffer(SQL_SELECT_ALL_ELEMENTS);
			
			if (filters.length() > 0){
				sql.append(SQL_WHERE);
				sql.append(filters);
			}
			
			sql.append(SQL_SELECT_ALL_ELEMENTS_GROUP_BY);
			sql.append(SQL_SELECT_ALL_ELEMENTS_ORDER_BY);
			
			pstm = prepareSQL(sql.toString(), dsName);
			rs = executeQuery(pstm);
			
			while (rs.next()){
				dto = new POCosts();
				
				dto.setAmount(rs.getDouble("amount"));
				dto.setBiweekPeriodVersionId(rs.getInt("biweek_period_version_id"));
				dto.setCapacity(rs.getDouble("capacity"));
				dto.setCodeLetter(rs.getString("code").trim());
				dto.setCodeNumber(rs.getInt("code_number"));
				dto.setCode(dto.getCodeLetter() + StringUtils.leftPad("" + dto.getCodeNumber(), 3, "0"));
				dto.setCornCode(rs.getString("corn_code"));
				dto.setCornDescription(rs.getString("corn_description"));
				dto.setCornOriginId(rs.getString("corn_origin_id"));
				dto.setMonthPeriodVersionId(rs.getInt("month_period_version_id"));
				dto.setPurchaseOrderVersionId(rs.getInt("purchase_order_version_id"));
								
				array.add(dto);
			}
			
			array.trimToSize();
			
		}catch (PoolException poolException){
			Exception e = null;
			poolException.printStackTrace(System.out);
			e = new Exception(poolException);
			throw e;
		} catch(SQLException sqle){
			Exception e = null;
			sqle.printStackTrace(System.out);
			e = new Exception(sqle);
			throw e;
		}finally {
			try{ closeConnection(pstm.getConnection());	}catch(Exception exception){}
		}	
		
		return array;		
	}
	
	public POCosts getById(int purchaseOrderVersionId, String filters) throws Exception {
		StringBuffer sql = null;
		POCosts dto = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try{
			sql = new StringBuffer(SQL_SELECT_ALL_ELEMENTS);
			
			sql.append(SQL_WHERE);
			
			sql.append(" pov.purchase_order_version_id = " + purchaseOrderVersionId);
			
			if (filters.length() > 0){
				sql.append(filters);
			}
			
			sql.append(SQL_SELECT_ALL_ELEMENTS_GROUP_BY);
			sql.append(SQL_SELECT_ALL_ELEMENTS_ORDER_BY);
			
			pstm = prepareSQL(sql.toString(), dsName);
			rs = executeQuery(pstm);
			
			if (rs.next()){
				dto = new POCosts();
				
				dto.setAmount(rs.getDouble("amount"));
				dto.setBiweekPeriodVersionId(rs.getInt("biweek_period_version_id"));
				dto.setCapacity(rs.getDouble("capacity"));
				dto.setCodeLetter(rs.getString("code").trim());
				dto.setCodeNumber(rs.getInt("code_number"));
				dto.setCode(dto.getCodeLetter() + StringUtils.leftPad("" + dto.getCodeNumber(), 3, "0"));
				dto.setCornCode(rs.getString("corn_code"));
				dto.setCornDescription(rs.getString("corn_description"));
				dto.setCornOriginId(rs.getString("corn_origin_id"));
				dto.setMonthPeriodVersionId(rs.getInt("month_period_version_id"));
				dto.setPurchaseOrderVersionId(rs.getInt("purchase_order_version_id"));
			}
			
		}catch (PoolException poolException){
			Exception e = null;
			poolException.printStackTrace(System.out);
			e = new Exception(poolException);
			throw e;
		} catch(SQLException sqle){
			Exception e = null;
			sqle.printStackTrace(System.out);
			e = new Exception(sqle);
			throw e;
		}finally {
			try{ closeConnection(pstm.getConnection());	}catch(Exception exception){}
		}	
		
		return dto;		
	}
	
	
	public POCosts getByPOIdCEId(int purchaseOrderVersionId, String costElementId) throws Exception {
		StringBuffer sql = null;
		POCosts dto = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try{
			sql = new StringBuffer(SQL_GET_POID_CEID);
			
			pstm = prepareSQL(sql.toString(), dsName);
			
			pstm.setInt(1, purchaseOrderVersionId);
			pstm.setString(2, costElementId);
			
			rs = executeQuery(pstm);
			
			if (rs.next()){
				dto = new POCosts();
				
				dto.setAmount(rs.getDouble("amount"));
				dto.setPurchaseOrderVersionId(rs.getInt("purchase_order_version_id"));
			}
			
		}catch (PoolException poolException){
			Exception e = null;
			poolException.printStackTrace(System.out);
			e = new Exception(poolException);
			throw e;
		} catch(SQLException sqle){
			Exception e = null;
			sqle.printStackTrace(System.out);
			e = new Exception(sqle);
			throw e;
		}finally {
			try{ closeConnection(pstm.getConnection());	}catch(Exception exception){}
		}	
		
		return dto;		
	}
	
	public ArrayList getAllByPOIdCEId(int versionId) throws Exception {
		ArrayList array			= null;
		StringBuffer sql 		= null;
		POCostsByElements dto 	= null;
		PreparedStatement pstm 	= null;
		ResultSet rs 			= null;
		
		try{
			array	= new ArrayList(100);
			sql		= new StringBuffer(SQL_GET_ALL_POID_CEID);
			
			pstm = prepareSQL(sql.toString(), dsName);
			
			pstm.setInt(1, versionId);
			
			rs = executeQuery(pstm);
			
			while (rs.next()){
				dto = new POCostsByElements();
				
				dto.setAmount(rs.getDouble("amount"));
				dto.setCostElementId(rs.getInt("cost_element_id"));
				dto.setPurchaseOrderVersionId(rs.getInt("purchase_order_version_id"));
				dto.setShortName(rs.getString("short_name"));
				
				array.add(dto);
			}
			
			array.trimToSize();
			
		}catch (PoolException poolException){
			Exception e = null;
			poolException.printStackTrace(System.out);
			e = new Exception(poolException);
			throw e;
		} catch(SQLException sqle){
			Exception e = null;
			sqle.printStackTrace(System.out);
			e = new Exception(sqle);
			throw e;
		}finally {
			try{ closeConnection(pstm.getConnection());	}catch(Exception exception){}
		}	
		
		return array;		
	}
	
	public ArrayList getAllByPOIdCEIdRecurrent(int versionId, int costOrigin) throws Exception {
		ArrayList array			= null;
		StringBuffer sql 		= null;
		PORecurrentCost dto 	= null;
		PreparedStatement pstm 	= null;
		ResultSet rs 			= null;
		
		try{
			array	= new ArrayList(100);
			sql		= new StringBuffer(SQL_GET_ALL_POID_CEID_RECURRENT);
			
			pstm = prepareSQL(sql.toString(), dsName);
			
			pstm.setInt(1, versionId);
			pstm.setInt(2, costOrigin);
			
			rs = executeQuery(pstm);
			
			while (rs.next()){
				dto = new PORecurrentCost();
				
				dto.setAmount(rs.getDouble("amount"));
				dto.setCostElementId(rs.getInt("cost_element_id"));
				dto.setCostElementShortName(rs.getString("short_name"));
				dto.setMonthPeriodVersionId(rs.getInt("month_period_version_id"));
				dto.setPlantId(rs.getInt("plant_id"));
				dto.setPlantVersionId(rs.getInt("plant_version_id"));
				dto.setPurchaseOrderVersionId(rs.getInt("purchase_order_version_id"));
				
				array.add(dto);
			}
			
			array.trimToSize();
			
		}catch (PoolException poolException){
			Exception e = null;
			poolException.printStackTrace(System.out);
			e = new Exception(poolException);
			throw e;
		} catch(SQLException sqle){
			Exception e = null;
			sqle.printStackTrace(System.out);
			e = new Exception(sqle);
			throw e;
		}finally {
			try{ closeConnection(pstm.getConnection());	}catch(Exception exception){}
		}	
		
		return array;		
	}
	
	public ArrayList getAllByPOIdCEIdWarehouse(int versionId) throws Exception {
		ArrayList array			= null;
		StringBuffer sql 		= null;
		PORecurrentCost dto 	= null;
		PreparedStatement pstm 	= null;
		ResultSet rs 			= null;
		
		try{
			array	= new ArrayList(100);
			sql		= new StringBuffer(SQL_GET_ALL_POID_CEID_WAREHOUSE);
			
			pstm = prepareSQL(sql.toString(), dsName);
			
			pstm.setInt(1, versionId);
			
			rs = executeQuery(pstm);
			
			while (rs.next()){
				dto = new PORecurrentCost();
				
				dto.setAmount(rs.getDouble("amount"));
				dto.setMonthPeriodVersionId(rs.getInt("month_period_version_id"));
				dto.setPlantId(rs.getInt("plant_id"));
				dto.setPlantVersionId(rs.getInt("plant_version_id"));
				dto.setPurchaseOrderVersionId(rs.getInt("purchase_order_version_id"));
				
				array.add(dto);
			}
			
			array.trimToSize();
			
		}catch (PoolException poolException){
			Exception e = null;
			poolException.printStackTrace(System.out);
			e = new Exception(poolException);
			throw e;
		} catch(SQLException sqle){
			Exception e = null;
			sqle.printStackTrace(System.out);
			e = new Exception(sqle);
			throw e;
		}finally {
			try{ closeConnection(pstm.getConnection());	}catch(Exception exception){}
		}	
		
		return array;		
	}

}
